﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.Util;
using System.Threading;
using System.Xml;


namespace Excel_DEMO
{
    public partial class Form1 : Form
    {
        DirectoryInfo[] ListDir;
        FileInfo[] ListFile;
        Thread TransDataThread;
        string ExportFile;
        int TotalNum = 0;
        double DoneNum = 0.00;//计算百分比 避免Int整除被舍去
        int ErrorNum = 0;
        IWorkbook outputwk;//输出工作表

        public Form1()
        {
            Control.CheckForIllegalCrossThreadCalls = false;
            InitializeComponent();
        }

        private void Folder_Button_Click(object sender, EventArgs e)
        {
            TotalNum = 0;
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择文件路径";
            if(dialog.ShowDialog()==DialogResult.OK)
            {
                FolderPath_TextBox.Text = dialog.SelectedPath;
                ListFolder(dialog.SelectedPath);
                Output("点击-->开始录入<--按钮可以开始数据录入");
                TransData_Button.Enabled = true;
            }
        }

        /// <summary>
        /// 遍历子目录
        /// </summary>
        /// <param name="targetFolder">遍历的文件夹</param>
        private void ListFolder(string targetFolder)
        {
            DirectoryInfo fatherDir = new DirectoryInfo(targetFolder);
            ListDir = fatherDir.GetDirectories();
            Output("检索到" + ListDir.Length + "个文件夹");
            foreach(DirectoryInfo u in ListDir)
            {
                int temp = ListExcelFile(u.FullName);
                Output(u.ToString() + "-->" + temp.ToString() + "<--");
                TotalNum += temp;
            }
            Output("共找到" + TotalNum.ToString() + "个Excel文件");
        }

        /// <summary>
        /// 遍历Excel文件
        /// </summary>
        /// <param name="targetFolder">遍历的文件夹</param>
        /// <returns>找到Excel文件个数</returns>
        private int ListExcelFile(string targetFolder)
        {
            DirectoryInfo fatherDir = new DirectoryInfo(targetFolder);
            ListFile = fatherDir.GetFiles("*.xls");
            return ListFile.Length;
        }

        /// <summary>
        /// 输出信息函数
        /// </summary>
        /// <param name="str">输出的内容</param>
        private void Output(string str)
        {
            str+="\r\n";
            Output_TextBox.AppendText(str);
            Output_TextBox.ScrollToCaret();
        }

        private void TransData_Button_Click(object sender, EventArgs e)
        {
            OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.Title = "请选择模板EXCEL文件";
            fileDialog.Filter = "excel表格(*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                ExportFile = fileDialog.FileName;
                Output("指定输出文件->" + ExportFile);
            }
            TransDataThread = new Thread(new ThreadStart(TransData));
            TransDataThread.IsBackground = true;
            TransDataThread.Start();
        }

        /// <summary>
        /// 数据写入线程函数
        /// </summary>
        private void TransData()
        {
            DoneNum = 0.00;
            FileStream outputFS;
            try
            {
                outputFS = new FileStream(ExportFile, FileMode.Open, FileAccess.Read);
            }
            catch (Exception e)//若文件已被打开，则提示读取失败
            {
                Output("--------------------------------------------");
                Output("ERROR:" + e.GetType());
                Output(e.Message);
                Output("请关闭打开的EXCEL文件后重新开始录入");
                return;
            }
            outputwk = new HSSFWorkbook(outputFS);//载入输出文件流
            foreach (DirectoryInfo u in ListDir)
            {
                int row = 5 - 1;//写入起始行 从第5行开始写入
                Output("正在遍历" + u.ToString() + "文件夹");
                ListFile = null;
                ListExcelFile(u.FullName);
                foreach (FileInfo v in ListFile)
                {
                    Output("正在处理" + v.ToString() + "...");
                    FileStream fs;
                    try{
                        fs = File.OpenRead(v.FullName);
                    }
                    catch(Exception e)//若文件已被打开，则提示读取失败
                    {
                        Output("--------------------------------------------");
                        Output("ERROR:"+e.GetType());
                        Output(e.Message);
                        Output("请关闭打开的EXCEL文件后重新开始录入");
                        return;
                    }
                    IWorkbook hssfworkbook = new HSSFWorkbook(fs);
                    ISheet sheet = hssfworkbook.GetSheetAt(0);
                    ISheet outsheet = outputwk.GetSheet(u.ToString());//在录入文件中查找以问卷工作表命名的工作表
                    if (outsheet == null)
                    {
                        Output("--------------------------------------------");
                        Output("ERROR:模板文件中找不到-->" + u.ToString() + "<--工作表");
                        Output("请确认后重新录入");
                        return;
                    }

                    int lost1, lost2, lost3, lost4,cellnum=0;
                    string[] placeType, visitDate;
                    string inTime, outTime;
                    ICellStyle style;
                    switch(u.ToString())
                    {
                        case "卖场店（六十分制）":
                            cellnum = 38;
                            //SetStringValue(ref outputwk,row,1,""); //设置卖场店级别【未启用】
                            CopyValue(ref sheet, ref outsheet, 8 - 1, 3 - 1, row, 3 - 1);//设置所属区域
                            CopyValue(ref sheet, ref outsheet, 5 - 1, 3 - 1, row, 4 - 1);//设置营业厅名称
                            lost1 = GetIntValue(sheet, 20 - 1, 4 - 1)
                                + GetIntValue(sheet, 22 - 1, 4 - 1)
                                + GetIntValue(sheet, 23 - 1, 4 - 1);//设置环境体验分
                            SetIntValue(ref outsheet, row, 7 - 1,
                                20 - lost1);
                            lost2 = GetIntValue(sheet, 26 - 1, 4 - 1);
                            SetIntValue(ref outsheet, row, 8 - 1,
                                10 - lost2);//设置基础服务分
                            lost3 = GetIntValue(sheet, 29 - 1, 4 - 1)
                                + GetIntValue(sheet, 31 - 1, 4 - 1)
                                + GetIntValue(sheet, 33 - 1, 4 - 1)
                                + GetIntValue(sheet, 34 - 1, 4 - 1)
                                + GetIntValue(sheet, 35 - 1, 4 - 1)
                                + GetIntValue(sheet, 36 - 1, 4 - 1);//设置服务人员分
                            SetIntValue(ref outsheet, row, 9 - 1,
                                30 - lost3);
                            SetIntValue(ref outsheet, row, 5 - 1, 60-lost1-lost2-lost3);//设置总分
                            CopyValue(ref sheet, ref outsheet, 39 - 1, 2 - 1, row, 10 - 1);//设置好的地方
                            CopyValue(ref sheet, ref outsheet, 41 - 1, 2 - 1, row, 11 - 1);//设置改进地方
                            placeType = sheet.GetRow(10 - 1).GetCell(2 - 1).ToString().Split('：');//设置营业厅类型（从源表格中抓取）
                            SetStringValue(ref outsheet, row, 14 - 1, placeType[placeType.Length - 1].Trim());
                            visitDate = sheet.GetRow(11 - 1).GetCell(2 - 1).ToString().Split('：');//设置访问日期
                            SetStringValue(ref outsheet, row, 15 - 1, visitDate[visitDate.Length - 1].Trim());
                            inTime = sheet.GetRow(12 - 1).GetCell(2 - 1).ToString().Replace("进店时间：（填写格式为24小时制）：","");//设置进店时间
                            SetStringValue(ref outsheet, row, 16 - 1, inTime.Trim());
                            outTime = sheet.GetRow(13 - 1).GetCell(2 - 1).ToString().Replace("结束时间：（填写格式为24小时制）：","");//设置离店时间
                            SetStringValue(ref outsheet, row, 17 - 1, outTime.Trim());
                            CopyValue(ref sheet, ref outsheet, 14 - 1, 3 - 1, row, 18 - 1);//设置访员姓名
                            CopyValue(ref sheet, ref outsheet, 20 - 1, 4 - 1, row, 19 - 1);//设置A1失分
                            CopyValue(ref sheet, ref outsheet, 20 - 1, 5 - 1, row, 20 - 1);//设置A1原因
                            CopyValue(ref sheet, ref outsheet, 22 - 1, 4 - 1, row, 21 - 1);//设置A2失分
                            CopyValue(ref sheet, ref outsheet, 22 - 1, 5 - 1, row, 22 - 1);//设置A2原因
                            CopyValue(ref sheet, ref outsheet, 23 - 1, 4 - 1, row, 23 - 1);//设置A3失分
                            CopyValue(ref sheet, ref outsheet, 23 - 1, 5 - 1, row, 24 - 1);//设置A3原因
                            CopyValue(ref sheet, ref outsheet, 26 - 1, 4 - 1, row, 25 - 1);//设置B1失分
                            CopyValue(ref sheet, ref outsheet, 26 - 1, 5 - 1, row, 26 - 1);//设置B1原因
                            CopyValue(ref sheet, ref outsheet, 29 - 1, 4 - 1, row, 27 - 1);//设置C1失分
                            CopyValue(ref sheet, ref outsheet, 29 - 1, 5 - 1, row, 28 - 1);//设置C1原因
                            CopyValue(ref sheet, ref outsheet, 31 - 1, 4 - 1, row, 29 - 1);//设置C2失分
                            CopyValue(ref sheet, ref outsheet, 31 - 1, 5 - 1, row, 30 - 1);//设置C2原因
                            CopyValue(ref sheet, ref outsheet, 33 - 1, 4 - 1, row, 31 - 1);//设置C3失分
                            CopyValue(ref sheet, ref outsheet, 33 - 1, 5 - 1, row, 32 - 1);//设置C3原因
                            CopyValue(ref sheet, ref outsheet, 34 - 1, 4 - 1, row, 33 - 1);//设置C4失分
                            CopyValue(ref sheet, ref outsheet, 34 - 1, 5 - 1, row, 34 - 1);//设置C4原因
                            CopyValue(ref sheet, ref outsheet, 35 - 1, 4 - 1, row, 35 - 1);//设置C5失分
                            CopyValue(ref sheet, ref outsheet, 35 - 1, 5 - 1, row, 36 - 1);//设置C5原因
                            CopyValue(ref sheet, ref outsheet, 36 - 1, 4 - 1, row, 37 - 1);//设置C6失分
                            CopyValue(ref sheet, ref outsheet, 36 - 1, 5 - 1, row, 38 - 1);//设置C6原因
                            break;
                        case "校园店（一百分制）":
                            cellnum = 63;
                            SetStringValue(ref outsheet,row,1,"四级厅");//设置卖场店级别，根据表格，校园店都是四级厅
                            CopyValue(ref sheet, ref outsheet, 8 - 1, 3 - 1, row, 3 - 1);//设置所属区域
                            CopyValue(ref sheet, ref outsheet, 5 - 1, 3 - 1, row, 4 - 1);//设置营业厅名称
                            lost1 = GetIntValue(sheet, 20 - 1, 4 - 1)
                                + GetIntValue(sheet, 21 - 1, 4 - 1)
                                + GetIntValue(sheet, 22 - 1, 4 - 1)
                                + GetIntValue(sheet, 23 - 1, 4 - 1)
                                + GetIntValue(sheet, 25 - 1, 4 - 1)
                                + GetIntValue(sheet, 26 - 1, 4 - 1)
                                + GetIntValue(sheet, 27 - 1, 4 - 1);//设置环境体验分
                            SetIntValue(ref outsheet, row, 7 - 1,
                                30 - lost1);
                            lost2 = GetIntValue(sheet, 30 - 1, 4 - 1)
                                + GetIntValue(sheet, 32 - 1, 4 - 1)
                                + GetIntValue(sheet, 33 - 1, 4 - 1)
                                + GetIntValue(sheet, 34 - 1, 4 - 1)
                                + GetIntValue(sheet, 35 - 1, 4 - 1)
                                + GetIntValue(sheet, 37 - 1, 4 - 1);//设置基础服务分
                            SetIntValue(ref outsheet, row, 8 - 1,
                                30 - lost2);
                            lost3 = GetIntValue(sheet, 40 - 1, 4 - 1)
                                + GetIntValue(sheet, 42 - 1, 4 - 1)
                                + GetIntValue(sheet, 44 - 1, 4 - 1)
                                + GetIntValue(sheet, 45 - 1, 4 - 1)
                                + GetIntValue(sheet, 46 - 1, 4 - 1);//设置服务人员分
                            SetIntValue(ref outsheet, row, 9 - 1,
                                20 - lost3);
                            lost4 = GetIntValue(sheet, 49 - 1, 4 - 1)
                                + GetIntValue(sheet, 51 - 1, 4 - 1)
                                + GetIntValue(sheet, 52 - 1, 4 - 1)
                                + GetIntValue(sheet, 54 - 1, 4 - 1);//设置应急处理分
                            SetIntValue(ref outsheet, row, 10 - 1,
                                20 - lost4);
                            SetIntValue(ref outsheet, row, 5 - 1, 100 - lost1 - lost2 - lost3 - lost4);//设置总分
                            CopyValue(ref sheet, ref outsheet, 57 - 1, 2 - 1, row, 11 - 1);//设置好的地方
                            CopyValue(ref sheet, ref outsheet, 60 - 1, 2 - 1, row, 12 - 1);//设置改进地方
                            placeType = sheet.GetRow(10 - 1).GetCell(2 - 1).ToString().Split('：');//设置营业厅类型（从源表格中抓取）
                            SetStringValue(ref outsheet, row, 15 - 1, placeType[placeType.Length - 1].Trim());
                            visitDate = sheet.GetRow(11 - 1).GetCell(2 - 1).ToString().Split('：');//设置访问日期
                            SetStringValue(ref outsheet, row, 16 - 1, visitDate[visitDate.Length - 1].Trim());
                            inTime = sheet.GetRow(12 - 1).GetCell(2 - 1).ToString().Replace("进店时间：（填写格式为24小时制）：","");//设置进店时间
                            SetStringValue(ref outsheet, row, 17 - 1, inTime.Trim());
                            outTime = sheet.GetRow(13 - 1).GetCell(2 - 1).ToString().Replace("结束时间：（填写格式为24小时制）：","");//设置离店时间
                            SetStringValue(ref outsheet, row, 18 - 1, outTime.Trim());
                            CopyValue(ref sheet, ref outsheet, 14 - 1, 3 - 1, row, 19 - 1);//设置访员姓名
                            CopyValue(ref sheet, ref outsheet, 20 - 1, 4 - 1, row, 20 - 1);//设置A1失分
                            CopyValue(ref sheet, ref outsheet, 20 - 1, 5 - 1, row, 21 - 1);//设置A1原因
                            CopyValue(ref sheet, ref outsheet, 21 - 1, 4 - 1, row, 22 - 1);//设置A2失分
                            CopyValue(ref sheet, ref outsheet, 21 - 1, 5 - 1, row, 23 - 1);//设置A2原因
                            CopyValue(ref sheet, ref outsheet, 22 - 1, 4 - 1, row, 24 - 1);//设置A3失分
                            CopyValue(ref sheet, ref outsheet, 22 - 1, 5 - 1, row, 25 - 1);//设置A3原因
                            CopyValue(ref sheet, ref outsheet, 23 - 1, 4 - 1, row, 26 - 1);//设置A4失分
                            CopyValue(ref sheet, ref outsheet, 23 - 1, 5 - 1, row, 27 - 1);//设置A4原因
                            CopyValue(ref sheet, ref outsheet, 25 - 1, 4 - 1, row, 28 - 1);//设置A5失分
                            CopyValue(ref sheet, ref outsheet, 25 - 1, 5 - 1, row, 29 - 1);//设置A5原因
                            CopyValue(ref sheet, ref outsheet, 26 - 1, 4 - 1, row, 30 - 1);//设置A6失分
                            CopyValue(ref sheet, ref outsheet, 26 - 1, 5 - 1, row, 31 - 1);//设置A6原因
                            CopyValue(ref sheet, ref outsheet, 27 - 1, 4 - 1, row, 32 - 1);//设置A7失分
                            CopyValue(ref sheet, ref outsheet, 27 - 1, 5 - 1, row, 33 - 1);//设置A7原因
                            CopyValue(ref sheet, ref outsheet, 30 - 1, 4 - 1, row, 34 - 1);//设置B1失分
                            CopyValue(ref sheet, ref outsheet, 30 - 1, 5 - 1, row, 35 - 1);//设置B1原因
                            CopyValue(ref sheet, ref outsheet, 32 - 1, 4 - 1, row, 36 - 1);//设置B2失分
                            CopyValue(ref sheet, ref outsheet, 32 - 1, 5 - 1, row, 37 - 1);//设置B2原因
                            CopyValue(ref sheet, ref outsheet, 33 - 1, 4 - 1, row, 38 - 1);//设置B3失分
                            CopyValue(ref sheet, ref outsheet, 33 - 1, 5 - 1, row, 39 - 1);//设置B3原因
                            CopyValue(ref sheet, ref outsheet, 34 - 1, 4 - 1, row, 40 - 1);//设置B4失分
                            CopyValue(ref sheet, ref outsheet, 34 - 1, 5 - 1, row, 41 - 1);//设置B4原因
                            CopyValue(ref sheet, ref outsheet, 35 - 1, 4 - 1, row, 42 - 1);//设置B5失分
                            CopyValue(ref sheet, ref outsheet, 35 - 1, 5 - 1, row, 43 - 1);//设置B5原因
                            CopyValue(ref sheet, ref outsheet, 37 - 1, 4 - 1, row, 44 - 1);//设置B6失分
                            CopyValue(ref sheet, ref outsheet, 37 - 1, 5 - 1, row, 45 - 1);//设置B6原因
                            CopyValue(ref sheet, ref outsheet, 40 - 1, 4 - 1, row, 46 - 1);//设置C1失分
                            CopyValue(ref sheet, ref outsheet, 40 - 1, 5 - 1, row, 47 - 1);//设置C1原因
                            CopyValue(ref sheet, ref outsheet, 42 - 1, 4 - 1, row, 48 - 1);//设置C2失分
                            CopyValue(ref sheet, ref outsheet, 42 - 1, 5 - 1, row, 49 - 1);//设置C2原因
                            CopyValue(ref sheet, ref outsheet, 44 - 1, 4 - 1, row, 50 - 1);//设置C3失分
                            CopyValue(ref sheet, ref outsheet, 44 - 1, 5 - 1, row, 51 - 1);//设置C3原因
                            CopyValue(ref sheet, ref outsheet, 45 - 1, 4 - 1, row, 52 - 1);//设置C4失分
                            CopyValue(ref sheet, ref outsheet, 45 - 1, 5 - 1, row, 53 - 1);//设置C4原因
                            CopyValue(ref sheet, ref outsheet, 46 - 1, 4 - 1, row, 54 - 1);//设置C5失分
                            CopyValue(ref sheet, ref outsheet, 46 - 1, 5 - 1, row, 55 - 1);//设置C5原因
                            CopyValue(ref sheet, ref outsheet, 49 - 1, 4 - 1, row, 56 - 1);//设置D1失分
                            CopyValue(ref sheet, ref outsheet, 49 - 1, 5 - 1, row, 57 - 1);//设置D1原因
                            CopyValue(ref sheet, ref outsheet, 51 - 1, 4 - 1, row, 58 - 1);//设置D2失分
                            CopyValue(ref sheet, ref outsheet, 51 - 1, 5 - 1, row, 59 - 1);//设置D2原因
                            CopyValue(ref sheet, ref outsheet, 52 - 1, 4 - 1, row, 60 - 1);//设置D3失分
                            CopyValue(ref sheet, ref outsheet, 52 - 1, 5 - 1, row, 61 - 1);//设置D3原因
                            CopyValue(ref sheet, ref outsheet, 54 - 1, 4 - 1, row, 62 - 1);//设置D4失分
                            CopyValue(ref sheet, ref outsheet, 54 - 1, 5 - 1, row, 63 - 1);//设置D4原因
                            break;
                        case "专营店（一百分制）":
                            cellnum = 63;
                            SetStringValue(ref outsheet,row,1,"四级厅");//设置卖场店级别，根据表格，专营店都是四级厅
                            CopyValue(ref sheet, ref outsheet, 8 - 1, 3 - 1, row, 3 - 1);//设置所属区域
                            CopyValue(ref sheet, ref outsheet, 5 - 1, 3 - 1, row, 4 - 1);//设置营业厅名称
                            lost1 = GetIntValue(sheet, 20 - 1, 4 - 1)
                                + GetIntValue(sheet, 21 - 1, 4 - 1)
                                + GetIntValue(sheet, 22 - 1, 4 - 1)
                                + GetIntValue(sheet, 23 - 1, 4 - 1)
                                + GetIntValue(sheet, 25 - 1, 4 - 1)
                                + GetIntValue(sheet, 26 - 1, 4 - 1)
                                + GetIntValue(sheet, 27 - 1, 4 - 1);//设置环境体验分
                            SetIntValue(ref outsheet, row, 7 - 1,
                                30 - lost1);
                            lost2 = GetIntValue(sheet, 30 - 1, 4 - 1)
                                + GetIntValue(sheet, 32 - 1, 4 - 1)
                                + GetIntValue(sheet, 33 - 1, 4 - 1)
                                + GetIntValue(sheet, 34 - 1, 4 - 1)
                                + GetIntValue(sheet, 35 - 1, 4 - 1)
                                + GetIntValue(sheet, 37 - 1, 4 - 1);//设置基础服务分
                            SetIntValue(ref outsheet, row, 8 - 1,
                                30 - lost2);
                            lost3 = GetIntValue(sheet, 40 - 1, 4 - 1)
                                + GetIntValue(sheet, 42 - 1, 4 - 1)
                                + GetIntValue(sheet, 44 - 1, 4 - 1)
                                + GetIntValue(sheet, 45 - 1, 4 - 1)
                                + GetIntValue(sheet, 46 - 1, 4 - 1);//设置服务人员分
                            SetIntValue(ref outsheet, row, 9 - 1,
                                20 - lost3);
                            lost4 = GetIntValue(sheet, 49 - 1, 4 - 1)
                                + GetIntValue(sheet, 51 - 1, 4 - 1)
                                + GetIntValue(sheet, 52 - 1, 4 - 1)
                                + GetIntValue(sheet, 54 - 1, 4 - 1);//设置应急处理分
                            SetIntValue(ref outsheet, row, 10 - 1,
                                20 - lost4);
                            SetIntValue(ref outsheet, row, 5 - 1, 100 - lost1 - lost2 - lost3 - lost4);//设置总分
                            CopyValue(ref sheet, ref outsheet, 57 - 1, 2 - 1, row, 11 - 1);//设置好的地方
                            CopyValue(ref sheet, ref outsheet, 60 - 1, 2 - 1, row, 12 - 1);//设置改进地方
                            placeType = sheet.GetRow(10 - 1).GetCell(2 - 1).ToString().Split('：');//设置营业厅类型（从源表格中抓取）
                            SetStringValue(ref outsheet, row, 15 - 1, placeType[placeType.Length - 1].Trim());
                            visitDate = sheet.GetRow(11 - 1).GetCell(2 - 1).ToString().Split('：');//设置访问日期
                            SetStringValue(ref outsheet, row, 16 - 1, visitDate[visitDate.Length - 1].Trim());
                            inTime = sheet.GetRow(12 - 1).GetCell(2 - 1).ToString().Replace("进店时间：（填写格式为24小时制）：","");//设置进店时间
                            SetStringValue(ref outsheet, row, 17 - 1, inTime.Trim());
                            outTime = sheet.GetRow(13 - 1).GetCell(2 - 1).ToString().Replace("结束时间：（填写格式为24小时制）：","");//设置离店时间
                            SetStringValue(ref outsheet, row, 18 - 1, outTime.Trim());
                            CopyValue(ref sheet, ref outsheet, 14 - 1, 3 - 1, row, 19 - 1);//设置访员姓名
                            CopyValue(ref sheet, ref outsheet, 20 - 1, 4 - 1, row, 20 - 1);//设置A1失分
                            CopyValue(ref sheet, ref outsheet, 20 - 1, 5 - 1, row, 21 - 1);//设置A1原因
                            CopyValue(ref sheet, ref outsheet, 21 - 1, 4 - 1, row, 22 - 1);//设置A2失分
                            CopyValue(ref sheet, ref outsheet, 21 - 1, 5 - 1, row, 23 - 1);//设置A2原因
                            CopyValue(ref sheet, ref outsheet, 22 - 1, 4 - 1, row, 24 - 1);//设置A3失分
                            CopyValue(ref sheet, ref outsheet, 22 - 1, 5 - 1, row, 25 - 1);//设置A3原因
                            CopyValue(ref sheet, ref outsheet, 23 - 1, 4 - 1, row, 26 - 1);//设置A4失分
                            CopyValue(ref sheet, ref outsheet, 23 - 1, 5 - 1, row, 27 - 1);//设置A4原因
                            CopyValue(ref sheet, ref outsheet, 25 - 1, 4 - 1, row, 28 - 1);//设置A5失分
                            CopyValue(ref sheet, ref outsheet, 25 - 1, 5 - 1, row, 29 - 1);//设置A5原因
                            CopyValue(ref sheet, ref outsheet, 26 - 1, 4 - 1, row, 30 - 1);//设置A6失分
                            CopyValue(ref sheet, ref outsheet, 26 - 1, 5 - 1, row, 31 - 1);//设置A6原因
                            CopyValue(ref sheet, ref outsheet, 27 - 1, 4 - 1, row, 32 - 1);//设置A7失分
                            CopyValue(ref sheet, ref outsheet, 27 - 1, 5 - 1, row, 33 - 1);//设置A7原因
                            CopyValue(ref sheet, ref outsheet, 30 - 1, 4 - 1, row, 34 - 1);//设置B1失分
                            CopyValue(ref sheet, ref outsheet, 30 - 1, 5 - 1, row, 35 - 1);//设置B1原因
                            CopyValue(ref sheet, ref outsheet, 32 - 1, 4 - 1, row, 36 - 1);//设置B2失分
                            CopyValue(ref sheet, ref outsheet, 32 - 1, 5 - 1, row, 37 - 1);//设置B2原因
                            CopyValue(ref sheet, ref outsheet, 33 - 1, 4 - 1, row, 38 - 1);//设置B3失分
                            CopyValue(ref sheet, ref outsheet, 33 - 1, 5 - 1, row, 39 - 1);//设置B3原因
                            CopyValue(ref sheet, ref outsheet, 34 - 1, 4 - 1, row, 40 - 1);//设置B4失分
                            CopyValue(ref sheet, ref outsheet, 34 - 1, 5 - 1, row, 41 - 1);//设置B4原因
                            CopyValue(ref sheet, ref outsheet, 35 - 1, 4 - 1, row, 42 - 1);//设置B5失分
                            CopyValue(ref sheet, ref outsheet, 35 - 1, 5 - 1, row, 43 - 1);//设置B5原因
                            CopyValue(ref sheet, ref outsheet, 37 - 1, 4 - 1, row, 44 - 1);//设置B6失分
                            CopyValue(ref sheet, ref outsheet, 37 - 1, 5 - 1, row, 45 - 1);//设置B6原因
                            CopyValue(ref sheet, ref outsheet, 40 - 1, 4 - 1, row, 46 - 1);//设置C1失分
                            CopyValue(ref sheet, ref outsheet, 40 - 1, 5 - 1, row, 47 - 1);//设置C1原因
                            CopyValue(ref sheet, ref outsheet, 42 - 1, 4 - 1, row, 48 - 1);//设置C2失分
                            CopyValue(ref sheet, ref outsheet, 42 - 1, 5 - 1, row, 49 - 1);//设置C2原因
                            CopyValue(ref sheet, ref outsheet, 44 - 1, 4 - 1, row, 50 - 1);//设置C3失分
                            CopyValue(ref sheet, ref outsheet, 44 - 1, 5 - 1, row, 51 - 1);//设置C3原因
                            CopyValue(ref sheet, ref outsheet, 45 - 1, 4 - 1, row, 52 - 1);//设置C4失分
                            CopyValue(ref sheet, ref outsheet, 45 - 1, 5 - 1, row, 53 - 1);//设置C4原因
                            CopyValue(ref sheet, ref outsheet, 46 - 1, 4 - 1, row, 54 - 1);//设置C5失分
                            CopyValue(ref sheet, ref outsheet, 46 - 1, 5 - 1, row, 55 - 1);//设置C5原因
                            CopyValue(ref sheet, ref outsheet, 49 - 1, 4 - 1, row, 56 - 1);//设置D1失分
                            CopyValue(ref sheet, ref outsheet, 49 - 1, 5 - 1, row, 57 - 1);//设置D1原因
                            CopyValue(ref sheet, ref outsheet, 51 - 1, 4 - 1, row, 58 - 1);//设置D2失分
                            CopyValue(ref sheet, ref outsheet, 51 - 1, 5 - 1, row, 59 - 1);//设置D2原因
                            CopyValue(ref sheet, ref outsheet, 52 - 1, 4 - 1, row, 60 - 1);//设置D3失分
                            CopyValue(ref sheet, ref outsheet, 52 - 1, 5 - 1, row, 61 - 1);//设置D3原因
                            CopyValue(ref sheet, ref outsheet, 54 - 1, 4 - 1, row, 62 - 1);//设置D4失分
                            CopyValue(ref sheet, ref outsheet, 54 - 1, 5 - 1, row, 63 - 1);//设置D4原因
                            break;
                        case "自有店（非卖场化改造店）":
                            cellnum = 73;
                            if ((sheet.GetRow(5 - 1).GetCell(3 - 1).ToString().Contains("瞻园")) || (sheet.GetRow(5 - 1).GetCell(3 - 1).ToString().Contains("大钟亭")))
                            {
                                SetStringValue(ref outsheet, row, 1, "二级厅");//设置卖场店级别，根据表格，南京瞻园营业厅和南京鼓楼大钟亭手机体验店是二级厅，在这里进行关键词匹配
                            }
                            else
                            {
                                SetStringValue(ref outsheet, row, 1, "三级厅");//其他都是三级厅
                            }
                            CopyValue(ref sheet, ref outsheet, 8 - 1, 3 - 1, row, 3 - 1);//设置所属区域
                            CopyValue(ref sheet, ref outsheet, 5 - 1, 3 - 1, row, 4 - 1);//设置营业厅名称
                            lost1 = GetIntValue(sheet, 20 - 1, 4 - 1)
                                + GetIntValue(sheet, 21 - 1, 4 - 1)
                                + GetIntValue(sheet, 22 - 1, 4 - 1)
                                + GetIntValue(sheet, 23 - 1, 4 - 1)
                                + GetIntValue(sheet, 25 - 1, 4 - 1)
                                + GetIntValue(sheet, 26 - 1, 4 - 1)
                                + GetIntValue(sheet, 27 - 1, 4 - 1)
                                + GetIntValue(sheet, 28 - 1, 4 - 1)
                                + GetIntValue(sheet, 29 - 1, 4 - 1);//设置环境体验分
                            SetIntValue(ref outsheet, row, 7 - 1,
                                30 - lost1);
                            lost2 = GetIntValue(sheet, 32 - 1, 4 - 1)
                                + GetIntValue(sheet, 34 - 1, 4 - 1)
                                + GetIntValue(sheet, 35 - 1, 4 - 1)
                                + GetIntValue(sheet, 36 - 1, 4 - 1)
                                + GetIntValue(sheet, 37 - 1, 4 - 1)
                                + GetIntValue(sheet, 38 - 1, 4 - 1)
                                + GetIntValue(sheet, 39 - 1, 4 - 1)
                                + GetIntValue(sheet, 40 - 1, 4 - 1)
                                + GetIntValue(sheet, 42 - 1, 4 - 1);//设置基础服务分
                            SetIntValue(ref outsheet, row, 8 - 1,
                                30 - lost2);
                            lost3 = GetIntValue(sheet, 45 - 1, 4 - 1)
                                + GetIntValue(sheet, 47 - 1, 4 - 1)
                                + GetIntValue(sheet, 49 - 1, 4 - 1)
                                + GetIntValue(sheet, 50 - 1, 4 - 1)
                                + GetIntValue(sheet, 51 - 1, 4 - 1);//设置服务人员分
                            SetIntValue(ref outsheet, row, 9 - 1,
                                20 - lost3);
                            lost4 = GetIntValue(sheet, 54 - 1, 4 - 1)
                                + GetIntValue(sheet, 56 - 1, 4 - 1)
                                + GetIntValue(sheet, 57 - 1, 4 - 1)
                                + GetIntValue(sheet, 59 - 1, 4 - 1);//设置应急处理分
                            SetIntValue(ref outsheet, row, 10 - 1,
                                20 - lost4);
                            SetIntValue(ref outsheet, row, 5 - 1, 100 - lost1 - lost2 - lost3 - lost4);//设置总分
                            CopyValue(ref sheet, ref outsheet, 62 - 1, 2 - 1, row, 11 - 1);//设置好的地方
                            CopyValue(ref sheet, ref outsheet, 65 - 1, 2 - 1, row, 12 - 1);//设置改进地方
                            SetStringValue(ref outsheet, row, 14 - 1, "必查");//设置访厅类型
                            placeType = sheet.GetRow(10 - 1).GetCell(2 - 1).ToString().Split('：');//设置营业厅类型（从源表格中抓取）
                            SetStringValue(ref outsheet, row, 15 - 1, placeType[placeType.Length - 1].Trim());
                            visitDate = sheet.GetRow(11 - 1).GetCell(2 - 1).ToString().Split('：');//设置访问日期
                            SetStringValue(ref outsheet, row, 16 - 1, visitDate[visitDate.Length - 1].Trim());
                            inTime = sheet.GetRow(12 - 1).GetCell(2 - 1).ToString().Replace("进店时间：（填写格式为24小时制）：","");//设置进店时间
                            SetStringValue(ref outsheet, row, 17 - 1, inTime.Trim());
                            outTime = sheet.GetRow(13 - 1).GetCell(2 - 1).ToString().Replace("结束时间：（填写格式为24小时制）：","");//设置离店时间
                            SetStringValue(ref outsheet, row, 18 - 1, outTime.Trim());
                            CopyValue(ref sheet, ref outsheet, 14 - 1, 3 - 1, row, 19 - 1);//设置访员姓名
                            CopyValue(ref sheet, ref outsheet, 20 - 1, 4 - 1, row, 20 - 1);//设置A1失分
                            CopyValue(ref sheet, ref outsheet, 20 - 1, 5 - 1, row, 21 - 1);//设置A1原因
                            CopyValue(ref sheet, ref outsheet, 21 - 1, 4 - 1, row, 22 - 1);//设置A2失分
                            CopyValue(ref sheet, ref outsheet, 21 - 1, 5 - 1, row, 23 - 1);//设置A2原因
                            CopyValue(ref sheet, ref outsheet, 22 - 1, 4 - 1, row, 24 - 1);//设置A3失分
                            CopyValue(ref sheet, ref outsheet, 22 - 1, 5 - 1, row, 25 - 1);//设置A3原因
                            CopyValue(ref sheet, ref outsheet, 23 - 1, 4 - 1, row, 26 - 1);//设置A4失分
                            CopyValue(ref sheet, ref outsheet, 23 - 1, 5 - 1, row, 27 - 1);//设置A4原因
                            CopyValue(ref sheet, ref outsheet, 25 - 1, 4 - 1, row, 28 - 1);//设置A5失分
                            CopyValue(ref sheet, ref outsheet, 25 - 1, 5 - 1, row, 29 - 1);//设置A5原因
                            CopyValue(ref sheet, ref outsheet, 26 - 1, 4 - 1, row, 30 - 1);//设置A6失分
                            CopyValue(ref sheet, ref outsheet, 26 - 1, 5 - 1, row, 31 - 1);//设置A6原因
                            CopyValue(ref sheet, ref outsheet, 27 - 1, 4 - 1, row, 32 - 1);//设置A7失分
                            CopyValue(ref sheet, ref outsheet, 27 - 1, 5 - 1, row, 33 - 1);//设置A7原因
                            CopyValue(ref sheet, ref outsheet, 28 - 1, 4 - 1, row, 34 - 1);//设置A8失分
                            CopyValue(ref sheet, ref outsheet, 28 - 1, 5 - 1, row, 35 - 1);//设置A8原因
                            CopyValue(ref sheet, ref outsheet, 29 - 1, 4 - 1, row, 36 - 1);//设置A9失分
                            CopyValue(ref sheet, ref outsheet, 29 - 1, 5 - 1, row, 37 - 1);//设置A9原因
                            CopyValue(ref sheet, ref outsheet, 32 - 1, 4 - 1, row, 38 - 1);//设置B1失分
                            CopyValue(ref sheet, ref outsheet, 32 - 1, 5 - 1, row, 39 - 1);//设置B1原因
                            CopyValue(ref sheet, ref outsheet, 34 - 1, 4 - 1, row, 40 - 1);//设置B2失分
                            CopyValue(ref sheet, ref outsheet, 34 - 1, 5 - 1, row, 41 - 1);//设置B2原因
                            CopyValue(ref sheet, ref outsheet, 35 - 1, 4 - 1, row, 42 - 1);//设置B3失分
                            CopyValue(ref sheet, ref outsheet, 35 - 1, 5 - 1, row, 43 - 1);//设置B3原因
                            CopyValue(ref sheet, ref outsheet, 36 - 1, 4 - 1, row, 44 - 1);//设置B4失分
                            CopyValue(ref sheet, ref outsheet, 36 - 1, 5 - 1, row, 45 - 1);//设置B4原因
                            CopyValue(ref sheet, ref outsheet, 37 - 1, 4 - 1, row, 46 - 1);//设置B5失分
                            CopyValue(ref sheet, ref outsheet, 37 - 1, 5 - 1, row, 47 - 1);//设置B5原因
                            CopyValue(ref sheet, ref outsheet, 38 - 1, 4 - 1, row, 48 - 1);//设置B6失分
                            CopyValue(ref sheet, ref outsheet, 38 - 1, 5 - 1, row, 49 - 1);//设置B6原因
                            CopyValue(ref sheet, ref outsheet, 39 - 1, 4 - 1, row, 50 - 1);//设置B7失分
                            CopyValue(ref sheet, ref outsheet, 39 - 1, 5 - 1, row, 51 - 1);//设置B7原因
                            CopyValue(ref sheet, ref outsheet, 40 - 1, 4 - 1, row, 52 - 1);//设置B8失分
                            CopyValue(ref sheet, ref outsheet, 40 - 1, 5 - 1, row, 53 - 1);//设置B8原因
                            CopyValue(ref sheet, ref outsheet, 42 - 1, 4 - 1, row, 54 - 1);//设置B9失分
                            CopyValue(ref sheet, ref outsheet, 42 - 1, 5 - 1, row, 55 - 1);//设置B9原因
                            CopyValue(ref sheet, ref outsheet, 45 - 1, 4 - 1, row, 56 - 1);//设置C1失分
                            CopyValue(ref sheet, ref outsheet, 45 - 1, 5 - 1, row, 57 - 1);//设置C1原因
                            CopyValue(ref sheet, ref outsheet, 47 - 1, 4 - 1, row, 58 - 1);//设置C2失分
                            CopyValue(ref sheet, ref outsheet, 47 - 1, 5 - 1, row, 59 - 1);//设置C2原因
                            CopyValue(ref sheet, ref outsheet, 49 - 1, 4 - 1, row, 60 - 1);//设置C3失分
                            CopyValue(ref sheet, ref outsheet, 49 - 1, 5 - 1, row, 61 - 1);//设置C3原因
                            CopyValue(ref sheet, ref outsheet, 50 - 1, 4 - 1, row, 62 - 1);//设置C4失分
                            CopyValue(ref sheet, ref outsheet, 50 - 1, 5 - 1, row, 63 - 1);//设置C4原因
                            CopyValue(ref sheet, ref outsheet, 51 - 1, 4 - 1, row, 64 - 1);//设置C5失分
                            CopyValue(ref sheet, ref outsheet, 51 - 1, 5 - 1, row, 65 - 1);//设置C5原因
                            CopyValue(ref sheet, ref outsheet, 54 - 1, 4 - 1, row, 66 - 1);//设置D1失分
                            CopyValue(ref sheet, ref outsheet, 54 - 1, 5 - 1, row, 67 - 1);//设置D1原因
                            CopyValue(ref sheet, ref outsheet, 56 - 1, 4 - 1, row, 68 - 1);//设置D2失分
                            CopyValue(ref sheet, ref outsheet, 56 - 1, 5 - 1, row, 69 - 1);//设置D2原因
                            CopyValue(ref sheet, ref outsheet, 57 - 1, 4 - 1, row, 70 - 1);//设置D3失分
                            CopyValue(ref sheet, ref outsheet, 57 - 1, 5 - 1, row, 71 - 1);//设置D3原因
                            CopyValue(ref sheet, ref outsheet, 59 - 1, 4 - 1, row, 72 - 1);//设置D4失分
                            CopyValue(ref sheet, ref outsheet, 59 - 1, 5 - 1, row, 73 - 1);//设置D4原因
                            break;
                        case "自有店（经卖场化改造后体验店）":
                            cellnum = 79;
                            SetStringValue(ref outsheet,row,1,"四级厅");//设置卖场店级别，根据表格，专营店都是四级厅
                            if ((sheet.GetRow(5 - 1).GetCell(3 - 1).ToString().Contains("瞻园")) || (sheet.GetRow(5 - 1).GetCell(3 - 1).ToString().Contains("大钟亭")))
                            {
                                SetStringValue(ref outsheet, row, 1, "二级厅");//设置卖场店级别，根据表格，南京瞻园营业厅和南京鼓楼大钟亭手机体验店是二级厅，在这里进行关键词匹配
                            }
                            else
                            {
                                SetStringValue(ref outsheet, row, 1, "三级厅");//其他都是三级厅
                            }
                            CopyValue(ref sheet, ref outsheet, 8 - 1, 3 - 1, row, 3 - 1);//设置所属区域
                            CopyValue(ref sheet, ref outsheet, 5 - 1, 3 - 1, row, 4 - 1);//设置营业厅名称
                            lost1 = GetIntValue(sheet, 20 - 1, 4 - 1)
                                + GetIntValue(sheet, 21 - 1, 4 - 1)
                                + GetIntValue(sheet, 22 - 1, 4 - 1)
                                + GetIntValue(sheet, 23 - 1, 4 - 1)
                                + GetIntValue(sheet, 25 - 1, 4 - 1)
                                + GetIntValue(sheet, 26 - 1, 4 - 1)
                                + GetIntValue(sheet, 27 - 1, 4 - 1)
                                + GetIntValue(sheet, 28 - 1, 4 - 1)
                                + GetIntValue(sheet, 29 - 1, 4 - 1);//设置环境体验分
                        //设置环境体验分
                            SetIntValue(ref outsheet, row, 7 - 1,
                                30 - lost1);
                            lost2 = GetIntValue(sheet, 32 - 1, 4 - 1)
                                + GetIntValue(sheet, 34 - 1, 4 - 1)
                                + GetIntValue(sheet, 35 - 1, 4 - 1)
                                + GetIntValue(sheet, 36 - 1, 4 - 1)
                                + GetIntValue(sheet, 37 - 1, 4 - 1)
                                + GetIntValue(sheet, 38 - 1, 4 - 1)
                                + GetIntValue(sheet, 39 - 1, 4 - 1)
                                + GetIntValue(sheet, 40 - 1, 4 - 1)
                                + GetIntValue(sheet, 42 - 1, 4 - 1)
                                + GetIntValue(sheet, 43 - 1, 4 - 1)
                                + GetIntValue(sheet, 45 - 1, 4 - 1);//设置基础服务分
                            SetIntValue(ref outsheet, row, 8 - 1,
                                30 - lost2);
                            lost3 = GetIntValue(sheet, 48 - 1, 4 - 1)
                                + GetIntValue(sheet, 50 - 1, 4 - 1)
                                + GetIntValue(sheet, 52 - 1, 4 - 1)
                                + GetIntValue(sheet, 53 - 1, 4 - 1)
                                + GetIntValue(sheet, 54 - 1, 4 - 1);//设置服务人员分
                            SetIntValue(ref outsheet, row, 9 - 1,
                                20 - lost3);
                            lost4 = GetIntValue(sheet, 57 - 1, 4 - 1)
                                + GetIntValue(sheet, 59 - 1, 4 - 1)
                                + GetIntValue(sheet, 60 - 1, 4 - 1)
                                + GetIntValue(sheet, 62 - 1, 4 - 1);//设置应急处理分
                            SetIntValue(ref outsheet, row, 10 - 1,
                                20 - lost4);
                            SetIntValue(ref outsheet, row, 5 - 1, 100 - lost1 - lost2 - lost3 - lost4);//设置总分
                            CopyValue(ref sheet, ref outsheet, 65 - 1, 2 - 1, row, 11 - 1);//设置好的地方
                            CopyValue(ref sheet, ref outsheet, 68 - 1, 2 - 1, row, 12 - 1);//设置改进地方
                            SetStringValue(ref outsheet, row, 14 - 1, "必查");//设置访厅类型
                            placeType = sheet.GetRow(10 - 1).GetCell(2 - 1).ToString().Split('：');//设置营业厅类型（从源表格中抓取）
                            SetStringValue(ref outsheet, row, 15 - 1, placeType[placeType.Length - 1].Trim());
                            visitDate = sheet.GetRow(11 - 1).GetCell(2 - 1).ToString().Split('：');//设置访问日期
                            SetStringValue(ref outsheet, row, 16 - 1, visitDate[visitDate.Length - 1].Trim());
                            inTime = sheet.GetRow(12 - 1).GetCell(2 - 1).ToString().Replace("进店时间：（填写格式为24小时制）：","");//设置进店时间
                            SetStringValue(ref outsheet, row, 17 - 1, inTime.Trim());
                            outTime = sheet.GetRow(13 - 1).GetCell(2 - 1).ToString().Replace("结束时间：（填写格式为24小时制）：","");//设置离店时间
                            SetStringValue(ref outsheet, row, 18 - 1, outTime.Trim());
                            CopyValue(ref sheet, ref outsheet, 14 - 1, 3 - 1, row, 19 - 1);//设置访员姓名
                            CopyValue(ref sheet, ref outsheet, 20 - 1, 4 - 1, row, 20 - 1);//设置A1失分
                            CopyValue(ref sheet, ref outsheet, 20 - 1, 5 - 1, row, 21 - 1);//设置A1原因
                            CopyValue(ref sheet, ref outsheet, 21 - 1, 4 - 1, row, 22 - 1);//设置A2失分
                            CopyValue(ref sheet, ref outsheet, 21 - 1, 5 - 1, row, 23 - 1);//设置A2原因
                            CopyValue(ref sheet, ref outsheet, 22 - 1, 4 - 1, row, 24 - 1);//设置A3失分
                            CopyValue(ref sheet, ref outsheet, 22 - 1, 5 - 1, row, 25 - 1);//设置A3原因
                            CopyValue(ref sheet, ref outsheet, 23 - 1, 4 - 1, row, 26 - 1);//设置A4失分
                            CopyValue(ref sheet, ref outsheet, 23 - 1, 5 - 1, row, 27 - 1);//设置A4原因
                            CopyValue(ref sheet, ref outsheet, 25 - 1, 4 - 1, row, 28 - 1);//设置A5失分
                            CopyValue(ref sheet, ref outsheet, 25 - 1, 5 - 1, row, 29 - 1);//设置A5原因
                            CopyValue(ref sheet, ref outsheet, 26 - 1, 4 - 1, row, 30 - 1);//设置A6失分
                            CopyValue(ref sheet, ref outsheet, 26 - 1, 5 - 1, row, 31 - 1);//设置A6原因
                            CopyValue(ref sheet, ref outsheet, 27 - 1, 4 - 1, row, 32 - 1);//设置A7失分
                            CopyValue(ref sheet, ref outsheet, 27 - 1, 5 - 1, row, 33 - 1);//设置A7原因
                            CopyValue(ref sheet, ref outsheet, 28 - 1, 4 - 1, row, 34 - 1);//设置A8失分
                            CopyValue(ref sheet, ref outsheet, 28 - 1, 5 - 1, row, 35 - 1);//设置A8原因
                            CopyValue(ref sheet, ref outsheet, 29 - 1, 4 - 1, row, 36 - 1);//设置A9失分
                            CopyValue(ref sheet, ref outsheet, 29 - 1, 5 - 1, row, 37 - 1);//设置A9原因
                            CopyValue(ref sheet, ref outsheet, 32 - 1, 4 - 1, row, 38 - 1);//设置B1失分
                            CopyValue(ref sheet, ref outsheet, 32 - 1, 5 - 1, row, 39 - 1);//设置B1原因
                            CopyValue(ref sheet, ref outsheet, 34 - 1, 4 - 1, row, 40 - 1);//设置B2失分
                            CopyValue(ref sheet, ref outsheet, 34 - 1, 5 - 1, row, 41 - 1);//设置B2原因
                            CopyValue(ref sheet, ref outsheet, 35 - 1, 4 - 1, row, 42 - 1);//设置B3失分
                            CopyValue(ref sheet, ref outsheet, 35 - 1, 5 - 1, row, 43 - 1);//设置B3原因
                            CopyValue(ref sheet, ref outsheet, 36 - 1, 4 - 1, row, 44 - 1);//设置B4失分
                            CopyValue(ref sheet, ref outsheet, 36 - 1, 5 - 1, row, 45 - 1);//设置B4原因
                            CopyValue(ref sheet, ref outsheet, 37 - 1, 4 - 1, row, 46 - 1);//设置B5失分
                            CopyValue(ref sheet, ref outsheet, 37 - 1, 5 - 1, row, 47 - 1);//设置B5原因
                            CopyValue(ref sheet, ref outsheet, 38 - 1, 4 - 1, row, 48 - 1);//设置B6失分
                            CopyValue(ref sheet, ref outsheet, 38 - 1, 5 - 1, row, 49 - 1);//设置B6原因
                            CopyValue(ref sheet, ref outsheet, 39 - 1, 4 - 1, row, 50 - 1);//设置B7失分
                            CopyValue(ref sheet, ref outsheet, 39 - 1, 5 - 1, row, 51 - 1);//设置B7原因
                            CopyValue(ref sheet, ref outsheet, 40 - 1, 4 - 1, row, 52 - 1);//设置B8失分
                            CopyValue(ref sheet, ref outsheet, 40 - 1, 5 - 1, row, 53 - 1);//设置B8原因
                            CopyValue(ref sheet, ref outsheet, 41 - 1, 4 - 1, row, 54 - 1);//设置B9失分
                            CopyValue(ref sheet, ref outsheet, 41 - 1, 5 - 1, row, 55 - 1);//设置B9原因
                            CopyValue(ref sheet, ref outsheet, 42 - 1, 4 - 1, row, 56 - 1);//设置B10失分
                            CopyValue(ref sheet, ref outsheet, 42 - 1, 5 - 1, row, 57 - 1);//设置B10原因
                            CopyValue(ref sheet, ref outsheet, 43 - 1, 4 - 1, row, 58 - 1);//设置B11失分
                            CopyValue(ref sheet, ref outsheet, 43 - 1, 5 - 1, row, 59 - 1);//设置B11原因
                            CopyValue(ref sheet, ref outsheet, 45 - 1, 4 - 1, row, 60 - 1);//设置B12失分
                            CopyValue(ref sheet, ref outsheet, 45 - 1, 5 - 1, row, 61 - 1);//设置B12原因
                            CopyValue(ref sheet, ref outsheet, 48 - 1, 4 - 1, row, 62 - 1);//设置C1失分
                            CopyValue(ref sheet, ref outsheet, 48 - 1, 5 - 1, row, 63 - 1);//设置C1原因
                            CopyValue(ref sheet, ref outsheet, 50 - 1, 4 - 1, row, 64 - 1);//设置C2失分
                            CopyValue(ref sheet, ref outsheet, 50 - 1, 5 - 1, row, 65 - 1);//设置C2原因
                            CopyValue(ref sheet, ref outsheet, 52 - 1, 4 - 1, row, 66 - 1);//设置C3失分
                            CopyValue(ref sheet, ref outsheet, 52 - 1, 5 - 1, row, 67 - 1);//设置C3原因
                            CopyValue(ref sheet, ref outsheet, 53 - 1, 4 - 1, row, 68 - 1);//设置C4失分
                            CopyValue(ref sheet, ref outsheet, 53 - 1, 5 - 1, row, 69 - 1);//设置C4原因
                            CopyValue(ref sheet, ref outsheet, 54 - 1, 4 - 1, row, 70 - 1);//设置C5失分
                            CopyValue(ref sheet, ref outsheet, 54 - 1, 5 - 1, row, 71 - 1);//设置C5原因
                            CopyValue(ref sheet, ref outsheet, 57 - 1, 4 - 1, row, 72 - 1);//设置D1失分
                            CopyValue(ref sheet, ref outsheet, 57 - 1, 5 - 1, row, 73 - 1);//设置D1原因
                            CopyValue(ref sheet, ref outsheet, 59 - 1, 4 - 1, row, 74 - 1);//设置D2失分
                            CopyValue(ref sheet, ref outsheet, 59 - 1, 5 - 1, row, 75 - 1);//设置D2原因
                            CopyValue(ref sheet, ref outsheet, 60 - 1, 4 - 1, row, 76 - 1);//设置D3失分
                            CopyValue(ref sheet, ref outsheet, 60 - 1, 5 - 1, row, 77 - 1);//设置D3原因
                            CopyValue(ref sheet, ref outsheet, 62 - 1, 4 - 1, row, 78 - 1);//设置D4失分
                            CopyValue(ref sheet, ref outsheet, 62 - 1, 5 - 1, row, 79 - 1);//设置D4原因
                            break;
                        default:
                            Output("ERROR:未知表格名称-->" + u.ToString() + "<--");
                            Output("请检查后重新录入");
                            return;
                    }
                    style = outputwk.CreateCellStyle();
                    if (Wrap_CheckBox.Checked == true)//设置换行属性
                    {
                        style.WrapText = true;
                    }
                    if (Border_CheckBox.Checked == true)//设置边框属性
                    {
                        style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
                        style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
                        style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                        style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
                    }
                    for (int i = 1; i < cellnum; i++)//套用创建的格式
                    {
                        if (outsheet.GetRow(row).GetCell(i) == null)
                        {
                            outsheet.GetRow(row).CreateCell(i).CellStyle = style;
                        }
                        else
                        {
                            outsheet.GetRow(row).GetCell(i).CellStyle = style;
                        }
                    }
                    row++;
                    DoneNum++;
                    Trans_ProcessBar.Value = (int)((DoneNum / (double)TotalNum) * 100);
                    Process_Label.Text = ((int)((DoneNum / (double)TotalNum) * 100)).ToString() + "%";
                    fs.Close();
                }
           }
            Output("--------------------------------------------");
            Output("Result:总文件 "+TotalNum.ToString()+", 成功 "+DoneNum.ToString()+" ,失败 "+ErrorNum);
            Output("点击-->保存<--按钮保存处理好的EXCEL表格");
            Save_Button.Enabled = true;
            outputFS.Close();
            
        }

        private void Save_Button_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "excel表格(*.xls)|*.xls";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                string fName = saveFileDialog.FileName;
                FileStream savefs;
                try
                {
                    savefs = new FileStream(fName, FileMode.Create);
                }
                catch (System.Exception ex)
                {
                    Output("--------------------------------------------");
                    Output("ERROR:" + ex.GetType());
                    Output(ex.Message);
                    Output("请关闭打开的EXCEL文件后重新保存");
                    return;
                }
                
                outputwk.Write(savefs);
                savefs.Close();
                Output("--------------------------------------------");
                Output("已成功保存Excel到-->" + saveFileDialog.FileName );
            }
        }

        private void CopyValue(ref ISheet source, ref ISheet target, int sourceRow, int sourceCell, int targetRow, int targetCell)
        {
            string tempValue;
            try
            {
                if (target.GetRow(targetRow) == null)
                {
                    target.CreateRow(targetRow);
                }
                if (target.GetRow(targetRow).GetCell(targetCell) == null)
                {
                    target.GetRow(targetRow).CreateCell(targetCell);
                }

                if (source.GetRow(sourceRow).GetCell(sourceCell).ToString() == "")
                {
                    tempValue = "0";
                }
                else
                {
                    tempValue = source.GetRow(sourceRow).GetCell(sourceCell).ToString();
                }
                target.GetRow(targetRow).GetCell(targetCell).
                    SetCellValue(tempValue);
            }
            catch (System.Exception e)
            {
                Output(e.ToString());
                Output("ERROR:" + e.GetType());
                Output("source->" + source);
                Output("target->" + target);
                Output("sourceRow->" + sourceRow);
                Output("sourceCell->" + sourceCell);
                Output("targetRow->" + targetRow);
                Output("targetCell->" + targetCell);
                Output("value->" + source.GetRow(sourceRow).GetCell(sourceCell).ToString());
                Thread.CurrentThread.Abort();
            }
        }

        private void SetIntValue(ref ISheet target, int targetRow, int targetCell,int value)
        {
            if (target.GetRow(targetRow) == null)
            {
                target.CreateRow(targetRow);
            }
            if (target.GetRow(targetRow).GetCell(targetCell) == null)
            {
                target.GetRow(targetRow).CreateCell(targetCell);
            }
            target.GetRow(targetRow).GetCell(targetCell).SetCellValue(value);
        }

        private void SetStringValue(ref ISheet target, int targetRow, int targetCell, string value)
        {
            if (target.GetRow(targetRow) == null)
            {
                target.CreateRow(targetRow);
            }
            if (target.GetRow(targetRow).GetCell(targetCell) == null)
            {
                target.GetRow(targetRow).CreateCell(targetCell);
            }
            target.GetRow(targetRow).GetCell(targetCell).SetCellValue(value);
        }

        private int GetIntValue(ISheet target, int targetRow, int targetCell)
        {
            int returnValue = 0;
            try
            {
                if (target.GetRow(targetRow).GetCell(targetCell).ToString().Trim() != "")
                {
                    returnValue = int.Parse(target.GetRow(targetRow).GetCell(targetCell).ToString());
                } 
            }
            catch (System.Exception e)
            {
                Output(e.ToString());
                Output("ERROR:" + e.GetType());
                Output("targetRow->" + targetRow);
                Output("targetCell->" + targetCell);
                try
                {
                    Output("value->" + target.GetRow(targetRow).GetCell(targetCell).ToString());
                }
                catch
                {
                    target.CreateRow(targetRow).CreateCell(targetCell);
                    Output("value->" + target.GetRow(targetRow).GetCell(targetCell).ToString());
                }
                Output("检查后重新录入");
                
                Thread.CurrentThread.Abort();
            }
            return returnValue;
        }

    }
}
